package com.zhangtai.utils.Excel;

import com.alibaba.fastjson.util.IOUtils;


import com.zhangtai.utils.Excel.office.ExcelOffice;
import com.zhangtai.utils.Excel.office.SheetDO;
import com.zhangtai.utils.R;
import com.zhangtai.utils.StringUtils;
import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.zhangtai.utils.Excel.StyleUtils.center;


public class ExcelUtil {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private final static String excel2003 = ".xls";    //2003- 版本的excel
    private final static String excel2007 = ".xlsx";   //2007+ 版本的excel


    public static void excel(ExcelOffice excel, SheetDO sheetDO, String fileName, HttpServletResponse response) {
        try {
            String path = fileName + new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()) + ".xls";

            // 取得文件名。
            String filename = URLEncoder.encode(path, "UTF-8").replace("%28", "(").replace("%29", ")");
            // 以流的形式下载文件。
            // 清空response
            response.reset();
            // 设置response的Header
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename="
                    + filename);
            OutputStream toClient = response.getOutputStream();
            excel.write(sheetDO, toClient);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static List<List<String>> getListFromSheet(Sheet sheet) {
        List<List<String>> list = new ArrayList<>();
        for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
            Row row = sheet.getRow(j);
            List<String> stringList = new ArrayList<String>();
            for (int k = 0; k < row.getLastCellNum(); k++) {
                stringList.add(row.getCell(k).toString());
            }
            list.add(stringList);
        }
        return list;
    }

    public static List<Map<String, String>> getMapListFromSheet(Sheet sheet) {
        List<Map<String, String>> list = new ArrayList<>();
        // 读取行数
        int rowNumber = sheet.getPhysicalNumberOfRows();
        for (int j = 0; j < rowNumber; j++) {
            Row row = sheet.getRow(j);
            if (row.getCell(0) == null)
                break;
            String s = row.getCell(0).toString();
            if (s == null || s.trim().length() == 0)
                break;

            Map<String, String> map = new HashMap<>();
            for (int k = 0; k < row.getLastCellNum(); k++) {
                Cell cell = row.getCell(k);
                String value;
                if (cell == null) {
                    value = "";
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    short format = cell.getCellStyle().getDataFormat();
                    SimpleDateFormat sdf = null;
                    if (format == 14 || format == 31 || format == 57 || format == 58) {
                        //日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double val = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(val);
                        value = sdf.format(date);
                    } else if (format == 20 || format == 32) {
                        //时间
                        sdf = new SimpleDateFormat("HH:mm");
                        double val = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(val);
                        value = sdf.format(date);
                    } else {
                        BigDecimal bg = new BigDecimal(cell.getNumericCellValue());
                        value = bg.toString();
                    }
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    value = cell.getStringCellValue();
                } else {
                    value = cell.toString();
                }
                map.put("c" + k, value);
            }
            list.add(map);
        }
        return list;
    }

    public static Workbook saveToSheet(List<Map<String, String>> data) {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        for (int i = 0; i < data.size(); i++) {
            Map<String, String> map = data.get(i);
            Row row = sheet.createRow(i);
            for (String key : map.keySet()) {
                int no = Integer.valueOf(key.substring(1));
                Cell cell = row.createCell(no);
                cell.setCellValue(map.get(key));
            }
        }
        return wb;
    }

    public static void saveToSheet(Workbook wb, List<Map<String, Object>> data) {
        saveToSheet(wb, data, 0);
    }

    public static void saveToSheet(Workbook wb, List<Map<String, Object>> data, int startIndex) {
        Sheet sheet = wb.createSheet();
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> map = data.get(i);
            Row row = sheet.createRow(startIndex + i);
            for (String key : map.keySet()) {
                int no = Integer.valueOf(key.substring(1));
                Cell cell = row.createCell(no);
                setCellValue(cell, map.get(key));
            }
        }
    }

    private static void setCellValue(Cell cell, Object obj) {
        if (obj == null) {
            cell.setCellValue("");
        } else if (obj instanceof Double) {
            cell.setCellValue((Double) obj);
        } else if (obj instanceof Date) {
            cell.setCellValue((Date) obj);
        } else {
            cell.setCellValue(obj.toString());
        }
    }

    public static void saveEntityToSheet(Workbook wb, List<? extends Entity> data) {
        saveEntityToSheet(wb, data, 0);
    }

    public static void saveEntityToSheet(Workbook wb, List<? extends Entity> data, int startIndex) {
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> map = data.get(i).toMap();
            Row row = sheet.createRow(startIndex + i);
            for (String key : map.keySet()) {
                int no = Integer.valueOf(key.substring(1));
                Cell cell = row.createCell(no);
                setCellValue(cell, map.get(key));
            }
        }
    }

    /**
     * 导出数据到excel返回response
     *
     * @param data           数据
     * @param startIndex     开始行
     * @param response       response对象
     * @param sampleFileName 模板文件
     * @param exportFileName 导出文件名
     * @param type           1或空 entity 2或其他 map
     * @throws IOException
     */
    @SuppressWarnings("unchecked")
    public static void export(List<?> data, int startIndex, HttpServletResponse response,
                              String sampleFileName, String exportFileName, Integer type) throws IOException {
        File file = new File(sampleFileName);
        InputStream is = null;
        Workbook workbook = null;
        try {
            is = new FileInputStream(file);
            workbook = new HSSFWorkbook(is);
        } catch (IOException e) {
            logger.error("导出数据异常", e);
        } finally {
            IOUtils.close(is);
        }
        if (workbook != null) {
            if (type == null || type == 1) {
                saveEntityToSheet(workbook, (List<? extends Entity>) data, startIndex);
            } else {
                saveToSheet(workbook, (List<Map<String, Object>>) data, startIndex);
            }

            OutputStream os = response.getOutputStream();
            // 清空response
            response.reset();
            // 设置response的Header
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(exportFileName, "UTF-8"));
            try {
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                logger.error("导出数据异常", e);
            } finally {
                IOUtils.close(os);
            }
        }
    }

    public static void exportMap(List<Map<String, Object>> data, int startIndex, HttpServletResponse response,
                                 String sampleFileName, String exportFileName) throws IOException {
        export(data, startIndex, response, sampleFileName, exportFileName, 2);
    }

    public static void exportEntity(List<? extends Entity> data, int startIndex, HttpServletResponse response,
                                    String sampleFileName, String exportFileName) throws IOException {
        export(data, startIndex, response, sampleFileName, exportFileName, 1);
    }

    /**
     * 获取实体对象返回属性名称
     *
     * @param clazz 实体对象
     * @return
     * @throws Exception
     */
    public static Field[] findEntityAllTypeName(Class clazz) throws Exception {

//		Class<? extends Object> cls = obj.getClass();

        return clazz.getDeclaredFields();
    }

    /**
     * excel生成列表
     *
     * @param file
     * @param titleRow
     * @param clazz
     * @return 成功时生成List, 失败时返回错误
     */
    public static R importBaseExcel(MultipartFile file, Integer titleRow, Class clazz) {
        if (titleRow == null || titleRow < 0) {
            titleRow = 0;
        }
        //把MultipartFile转化为File
        String filename = file.getOriginalFilename();
        try {
            if (filename.endsWith(excel2003)) {
                Workbook workbook = new HSSFWorkbook(file.getInputStream());  //2003-
                return importExcel2003(workbook, titleRow, clazz);
            } else if (filename.endsWith(excel2007)) {
                XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());  //2007+
                return importExcel2007(workbook, titleRow, clazz);
            } else {
                throw new Exception("解析的文件格式有误！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 导入excel2007
     *
     * @param workbook
     * @param titleRow
     * @param clazz
     * @return
     */
    private static R importExcel2007(XSSFWorkbook workbook, Integer titleRow, Class clazz) {
        if (titleRow == null || titleRow < 0) {
            titleRow = 0;
        }
        String ins = "导入数据出错：";
        boolean flag = true;
        try {
            //获取工作表
            XSSFSheet sheet = workbook.getSheetAt(0);
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            //获取该实体所有定义的属性 返回Field数组
            Field[] fields = ExcelUtil.findEntityAllTypeName(clazz);

            List list = new ArrayList();

            List<String> titleList = new ArrayList<String>();

            //循环插入数据
            f1:
            for (int i = titleRow; i <= lastRowNum; i++) {
                XSSFRow row = sheet.getRow(i);
                //获取标题列表
                if (i == titleRow) {
                    for (Cell cell : row) {
                        String title = cell.getStringCellValue();
                        if (!StringUtils.isEmpty(title)) {
                            titleList.add(title);
                        }
                    }
                } else {
                    boolean rflag = true;
                    //可以根据该类名生成Java对象
                    Object obj = clazz.newInstance();
                    f2:
                    for (int r = 0; r < row.getLastCellNum(); r++) {
                        Cell cell = row.getCell(r);
                        if (cell == null || cell.toString() == "") {
                            continue;
                        }
                        String title = titleList.get(r);
                        //除自增编号外，实体字段匹配sheet列
                        for (Field field : fields) {
                            //设置些属性是可以访问的
                            field.setAccessible(true);
                            //获取属性的类型
                            String type = field.getGenericType().toString();
                            boolean isAnn = field.isAnnotationPresent(ExcelAnnotation.class);
                            if (!isAnn) {
                                continue;
                            }
                            ExcelAnnotation ann = null;
                            try {
                                ann = field.getAnnotation(ExcelAnnotation.class);
                            } catch (Exception e) {
                                continue;
                            }
                            String annTitle = ann.rowName();
                            Boolean annIsMust = ann.isMust();

                            if (annTitle.equals(title)) {

                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                //判断是否为必填，必填为空时直接跳过这行数据
                                if (annIsMust && (cell == null || StringUtils.isEmpty(cell.getStringCellValue()))) {
                                    ins += (r + 1) + "行" + title + "列为空\\t";
                                    if (!rflag)
                                        flag = false;
                                    break f1;
                                } else if (cell == null) {
                                    break f2;
                                }
                                //如果参数为空
                                if (StringUtils.isEmpty(cell.toString())) {
                                    continue;
                                }
                                //根据属性类型装入值
                                switch (type) {
                                    case "char":
                                    case "java.lang.Character":
                                    case "class java.lang.String":
                                        field.set(obj, cell.getStringCellValue().trim());
                                        break;
                                    case "int":
                                    case "class java.lang.Integer":
                                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                        field.set(obj, Integer.valueOf((int) cell.getNumericCellValue()));
                                        break;
                                    case "class java.util.Date":
                                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                            Date d = cell.getDateCellValue();
                                            if (d != null) {
                                                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                                                field.set(obj, d);
                                            }
                                        } else {
                                            if (!StringUtils.isEmpty(cell.toString())) {
                                                Date date = org.apache.poi.ss.usermodel.DateUtil
                                                        .getJavaDate(Double.valueOf(cell.toString().trim()));
                                                field.set(obj, date);
                                            }
                                        }
                                        break;
                                    case "float":
                                    case "double":
                                    case "java.lang.Double":
                                    case "java.lang.Float":
                                    case "java.lang.Long":
                                    case "java.lang.Short":
                                    case "java.math.BigDecimal":
                                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                        field.set(obj, cell.getNumericCellValue());
                                        break;
                                    default:
                                        break;
                                }
                                rflag = false;
                                break;
                            }
                        }
                    }
                    if (!rflag)
                        list.add(obj);
                }
            }
            if (!flag)
                return R.error(ins);
            else
                return R.ok(list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//            try {
////                workbook.close();
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
        }
        return null;
    }

    /**
     * 导入excel
     *
     * @param workbook
     * @param titleRow
     * @param clazz
     * @return
     */
    public static R importExcel2003(Workbook workbook, Integer titleRow, Class clazz) {

        if (titleRow == null || titleRow < 0) {
            titleRow = 0;
        }
        String ins = "导入数据出错：";
        boolean flag = true;
        try {
            //获取工作表
            Sheet sheet = workbook.getSheetAt(0);
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            //获取该实体所有定义的属性 返回Field数组
            Field[] fields = ExcelUtil.findEntityAllTypeName(clazz);

            List list = new ArrayList();

            List<String> titleList = new ArrayList<String>();

            //循环插入数据
            f1:
            for (int i = titleRow; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                //获取标题列表
                if (i == titleRow) {
                    for (Cell cell : row) {
                        String title = cell.getStringCellValue();
                        if (!StringUtils.isEmpty(title)) {
                            titleList.add(title);
                        }
                    }
                } else {
                    boolean rflag = true;
                    //可以根据该类名生成Java对象
                    Object obj = clazz.newInstance();
                    f2:
                    for (int r = 0; r < row.getLastCellNum(); r++) {
                        Cell cell = row.getCell(r);
                        String title = titleList.get(r);
                        //除自增编号外，实体字段匹配sheet列
                        for (Field field : fields) {
                            //设置些属性是可以访问的
                            field.setAccessible(true);
                            //获取属性的类型
                            String type = field.getGenericType().toString();
                            boolean isAnn = field.isAnnotationPresent(ExcelAnnotation.class);
                            if (!isAnn) {
                                continue;
                            }
                            ExcelAnnotation ann = null;
                            try {
                                ann = field.getAnnotation(ExcelAnnotation.class);
                            } catch (Exception e) {
                                continue;
                            }
                            String annTitle = ann.rowName();
                            Boolean annIsMust = ann.isMust();

                            if (annTitle.equals(title)) {
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                //判断是否为必填，必填为空时直接跳过这行数据
                                if (annIsMust && (cell == null || StringUtils.isEmpty(cell.getStringCellValue()))) {
                                    ins += (r + 1) + "行" + title + "列为空\\t";
                                    if (!rflag)
                                        flag = false;
                                    break f1;
                                } else if (cell == null) {
                                    break f2;
                                }
                                //根据属性类型装入值
                                switch (type) {
                                    case "char":
                                    case "java.lang.Character":
                                    case "class java.lang.String":
                                        field.set(obj, cell.getStringCellValue());
                                        break;
                                    case "int":
                                    case "class java.lang.Integer":
//										cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                        if (!StringUtils.isEmpty(cell.getStringCellValue())) {
                                            field.set(obj, Integer.parseInt(cell.getStringCellValue()));
                                        }
                                        break;
                                    case "class java.util.Date":
                                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                            Date d = cell.getDateCellValue();
                                            if (d != null) {
                                                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                                                field.set(obj, d);
                                            }
                                        } else {
//											ALogFactory.getLog().info(cell.toString());
                                            if (!StringUtils.isEmpty(cell.toString())) {
                                                Date date = org.apache.poi.ss.usermodel.DateUtil
                                                        .getJavaDate(Double.valueOf(cell.toString()));
//                                            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                                field.set(obj, date);
                                            } else {
                                                break;
                                            }
                                        }
                                        break;
                                    case "float":
                                    case "double":
                                    case "java.lang.Double":
                                    case "java.lang.Float":
                                    case "java.lang.Long":
                                    case "java.lang.Short":
                                    case "java.math.BigDecimal":
                                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                        field.set(obj, cell.getNumericCellValue());
                                        break;
                                    default:
                                        break;
                                }
                                break;
                            }
                        }
                    }
                    if (!rflag)
                        list.add(obj);
                }
            }
            if (!flag)
                return R.error(ins);
            else
                return R.ok(list);

        } catch (Exception e) {
            e.printStackTrace();
        }
//		finally {
//			try {
//				workbook.
//				workbook.close();
//			} catch (IOException e) {
//				e.printStackTrace();
//			}
//		}
        return null;
    }

    /**
     * 根据文件选择excel版本
     *
     * @return
     * @throws Exception
     */
    public static Workbook chooseWorkbook(MultipartFile file) throws Exception {

        Workbook workbook = null;
        //把MultipartFile转化为File

        String filename = file.getOriginalFilename();

        if (filename.endsWith(excel2003)) {
            workbook = new HSSFWorkbook(file.getInputStream());  //2003-
        } else if (filename.endsWith(excel2007)) {
            workbook = new XSSFWorkbook(file.getInputStream());  //2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return workbook;
    }

    /**
     * 处理类型
     *
     * @param cell
     * @return
     */
    public static String getVal(Cell cell) {
        if (null != cell) {

            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC: // 数字

                    String val = cell.getNumericCellValue() + "";
                    int index = val.indexOf(".");

                    if (Integer.valueOf(val.substring(index + 1)) == 0) {
                        DecimalFormat df = new DecimalFormat("0");//处理科学计数法
                        return df.format(cell.getNumericCellValue());
                    }
                    return cell.getNumericCellValue() + "";//double
                case XSSFCell.CELL_TYPE_STRING: // 字符串
                    return cell.getStringCellValue() + "";
                case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    return cell.getBooleanCellValue() + "";
                case XSSFCell.CELL_TYPE_FORMULA: // 公式

                    try {
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            return (date.getYear() + 1900) + "-" + (date.getMonth() + 1) + "-" + date.getDate();
                        } else {
                            return String.valueOf((int) cell.getNumericCellValue());
                        }
                    } catch (IllegalStateException e) {
                        return String.valueOf(cell.getRichStringCellValue());
                    }
                case XSSFCell.CELL_TYPE_BLANK: // 空值
                    return "";
                case XSSFCell.CELL_TYPE_ERROR: // 故障
                    return "";
                default:
                    return "未知类型   ";
            }
        } else {
            return "";
        }
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称，默认sheet1
     * @param data      对象列表（对象实体需和clazz一样）
     * @param clazz     对象
     * @return
     */
    public static HSSFWorkbook exportExcel(String sheetName, List data, Class clazz) {
        return ExcelUtil.exportExcel(sheetName, data, clazz, false);
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称，默认sheet1
     * @param data      对象列表
     * @param clazz     对象
     * @param isTran    是否把对象列表转换成clazz对象，data为Map类型时使用（默认为false）
     * @return
     */
    public static HSSFWorkbook exportExcel(String sheetName, List data, Class clazz, Boolean isTran) {
        if (isTran == null)
            isTran = false;
        if (isTran)
            data = EntityUtil.ListMapToListObj(data, clazz);
        if (StringUtils.isEmpty(sheetName))
            sheetName = "sheet1";
        HSSFWorkbook wb = null;
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 400);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //创建标题
        //获取该实体所有定义的属性 返回Field数组
        Field[] fields = clazz.getDeclaredFields();
        int i = 0;
        for (Field field : fields) {
            //设置些属性是可以访问的
            field.setAccessible(true);
            //获取属性的类型
            String type = field.getGenericType().toString();
            boolean isAnn = field.isAnnotationPresent(ExcelAnnotation.class);
            if (!isAnn) {
                continue;
            }
            ExcelAnnotation ann = null;
            try {
                ann = field.getAnnotation(ExcelAnnotation.class);
            } catch (Exception e) {
                continue;
            }
            if (StringUtils.isEmpty(ann.rowName())) {
                continue;
            }
            //声明列对象
            HSSFCell cell = null;
            cell = row.createCell(i);
            cell.setCellValue(ann.rowName());
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, ann.width());
            i++;
        }
        HSSFDataFormat format = wb.createDataFormat();
        //创建内容
        for (int n = 0; n < data.size(); n++) {
            row = sheet.createRow(n + 1);
            row.setHeight((short) 400);
            Object obj = data.get(n);
            Class oClazz = obj.getClass();
            Field[] oFields = oClazz.getDeclaredFields();
            int j = 0;
            for (Field f : oFields) {
                //设置些属性是可以访问的
                f.setAccessible(true);
                //获取属性的类型
                String type = f.getGenericType().toString();
                boolean isAnn = f.isAnnotationPresent(ExcelAnnotation.class);
                if (!isAnn) {
                    continue;
                }
                ExcelAnnotation ann = null;
                try {
                    ann = f.getAnnotation(ExcelAnnotation.class);
                } catch (Exception e) {
                    continue;
                }
                if (StringUtils.isEmpty(ann.rowName())) {
                    continue;
                }
                HSSFCell cell = row.createCell(j);
                try {
                    if (!StringUtils.isEmpty(ann.notEmpty()) && f.get(obj) != null
                            && !StringUtils.isEmpty(f.get(obj).toString())) {
                        cell.setCellValue(ann.notEmpty());
                        j++;
                        continue;
                    }
                    if (f.get(obj) == null) {
                        j++;
                        continue;
                    }
                    //根据属性类型装入值
                    switch (type) {
                        case "char":
                        case "java.lang.Character":
                        case "class java.lang.String":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            cell.setCellValue(f.get(obj).toString());
                            break;
                        case "int":
                        case "class java.lang.Integer":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            cell.setCellValue(Integer.parseInt(f.get(obj).toString()));
                            break;
                        case "class java.util.Date":
//						    cell.getCellStyle().setDataFormat(format.getFormat("yyyy-m-d hh:mm"));
                            cell.setCellValue(f.get(obj).toString());
                            break;
                        case "float":
                        case "double":
                        case "class java.lang.Double":
                            String dString = new DecimalFormat("0").format(f.get(obj));
                            cell.setCellValue(dString);
                            break;
                        case "java.lang.Float":
                        case "java.lang.Long":
                        case "java.lang.Short":
                        case "class java.math.BigDecimal":
//							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//							String value = new BigDecimal() .toString();
                            cell.setCellValue(String.format("%.2f", f.get(obj)));
                            break;
                        default:
                            break;
                    }
                } catch (IllegalAccessException e) {
                    try {
                        System.out.println(f.get(obj).toString());
                    } catch (IllegalAccessException e1) {
                        e1.printStackTrace();
                    }
                    e.printStackTrace();
                }
                j++;
            }
        }
        //自动调整宽度
//        for(int a=0;a<i;a++) {
//            sheet.autoSizeColumn(a);
//        }
        return wb;
    }


    /**
     * 导出Excel2
     *
     * @param sheetName 模板名称（包括后缀）
     * @param dataRow   开始写入的数据行，默认为1
     * @param data      对象列表
     * @param clazz     对象
     * @param isTran    是否把对象列表转换成clazz对象，data为Map类型时使用（默认为false）
     * @return
     */
    public static XSSFWorkbook exportExcel2(String sheetName, Integer dataRow, List data, Class clazz, Boolean isTran) {
        if (isTran == null)
            isTran = false;
        if (isTran)
            data = EntityUtil.ListMapToListObj(data, clazz);
        if (StringUtils.isEmpty(sheetName))
            sheetName = "sheet1";
        XSSFWorkbook wb = null;
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new XSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row = sheet.createRow(dataRow);
        row.setHeight((short) 400);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //创建标题
        //获取该实体所有定义的属性 返回Field数组
        Field[] fields = clazz.getDeclaredFields();
        int i = 0;
        for (Field field : fields) {
            //设置些属性是可以访问的
            field.setAccessible(true);
            //获取属性的类型
            String type = field.getGenericType().toString();
            boolean isAnn = field.isAnnotationPresent(ExcelAnnotation.class);
            if (!isAnn) {
                continue;
            }
            ExcelAnnotation ann = null;
            try {
                ann = field.getAnnotation(ExcelAnnotation.class);
            } catch (Exception e) {
                continue;
            }
            if (StringUtils.isEmpty(ann.rowName())) {
                continue;
            }
            //声明列对象
            XSSFCell cell = null;
            cell = row.createCell(i);
            cell.setCellValue(ann.rowName());
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, ann.width());
            i++;
        }
        XSSFDataFormat format = wb.createDataFormat();
        //创建内容
        for (int n = 0; n < data.size(); n++) {
            row = sheet.createRow(n + dataRow + 1);
            row.setHeight((short) 400);
            Object obj = data.get(n);
            Class oClazz = obj.getClass();
            Field[] oFields = oClazz.getDeclaredFields();
            int j = 0;
            for (Field f : oFields) {
                //设置些属性是可以访问的
                f.setAccessible(true);
                //获取属性的类型
                String type = f.getGenericType().toString();
                boolean isAnn = f.isAnnotationPresent(ExcelAnnotation.class);
                if (!isAnn) {
                    continue;
                }
                ExcelAnnotation ann = null;
                try {
                    ann = f.getAnnotation(ExcelAnnotation.class);
                } catch (Exception e) {
                    continue;
                }
                if (StringUtils.isEmpty(ann.rowName())) {
                    continue;
                }
                XSSFCell cell = row.createCell(j);
                try {
                    if (!StringUtils.isEmpty(ann.notEmpty()) && f.get(obj) != null
                            && !StringUtils.isEmpty(f.get(obj).toString())) {
                        cell.setCellValue(ann.notEmpty());
                        j++;
                        continue;
                    }
                    if (f.get(obj) == null) {
                        j++;
                        continue;
                    }
                    //根据属性类型装入值
                    switch (type) {
                        case "char":
                        case "java.lang.Character":
                        case "class java.lang.String":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            cell.setCellValue(f.get(obj).toString());
                            break;
                        case "int":
                        case "class java.lang.Integer":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            cell.setCellValue(Integer.parseInt(f.get(obj).toString()));
                            break;
                        case "class java.util.Date":
//						    cell.getCellStyle().setDataFormat(format.getFormat("yyyy-m-d hh:mm"));
                            cell.setCellValue(f.get(obj).toString());
                            break;
                        case "float":
                        case "double":
                        case "class java.lang.Double":
                            String dString = new DecimalFormat("0").format(f.get(obj));
                            cell.setCellValue(dString);
                            break;
                        case "java.lang.Float":
                        case "java.lang.Long":
                        case "java.lang.Short":
                        case "class java.math.BigDecimal":
//							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//							String value = new BigDecimal() .toString();
                            cell.setCellValue(String.format("%.2f", f.get(obj)));
                            break;
                        default:
                            break;
                    }
                } catch (IllegalAccessException e) {
                    try {
                        System.out.println(f.get(obj).toString());
                    } catch (IllegalAccessException e1) {
                        e1.printStackTrace();
                    }
                    e.printStackTrace();
                }
                j++;
            }
        }
        return wb;
    }

    public static XSSFWorkbook exportExcel3(String sheetName, List data, Class clazz, Boolean isTran) {
        if (isTran == null)
            isTran = false;
        if (isTran)
            data = EntityUtil.ListMapToListObj(data, clazz);
        if (StringUtils.isEmpty(sheetName))
            sheetName = "sheet1";
        XSSFWorkbook wb = null;
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new XSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short) 400);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //创建标题
        //获取该实体所有定义的属性 返回Field数组
        Field[] fields = clazz.getDeclaredFields();
        int i = 0;
        for (Field field : fields) {
            //设置些属性是可以访问的
            field.setAccessible(true);
            //获取属性的类型
            String type = field.getGenericType().toString();
            boolean isAnn = field.isAnnotationPresent(ExcelAnnotation.class);
            if (!isAnn) {
                continue;
            }
            ExcelAnnotation ann = null;
            try {
                ann = field.getAnnotation(ExcelAnnotation.class);
            } catch (Exception e) {
                continue;
            }
            if (StringUtils.isEmpty(ann.rowName())) {
                continue;
            }
            //声明列对象
            XSSFCell cell = null;
            cell = row.createCell(i);
            cell.setCellValue(ann.rowName());
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, ann.width());
            i++;
        }
        XSSFDataFormat format = wb.createDataFormat();
        //创建内容
        for (int n = 0; n < data.size(); n++) {
            row = sheet.createRow(n + 1);
            row.setHeight((short) 400);
            Object obj = data.get(n);
            Class oClazz = obj.getClass();
            Field[] oFields = oClazz.getDeclaredFields();
            int j = 0;
            for (Field f : oFields) {
                //设置些属性是可以访问的
                f.setAccessible(true);
                //获取属性的类型
                String type = f.getGenericType().toString();
                boolean isAnn = f.isAnnotationPresent(ExcelAnnotation.class);
                if (!isAnn) {
                    continue;
                }
                ExcelAnnotation ann = null;
                try {
                    ann = f.getAnnotation(ExcelAnnotation.class);
                } catch (Exception e) {
                    continue;
                }
                if (StringUtils.isEmpty(ann.rowName())) {
                    continue;
                }
                XSSFCell cell = row.createCell(j);
                try {
                    if (!StringUtils.isEmpty(ann.notEmpty()) && f.get(obj) != null
                            && !StringUtils.isEmpty(f.get(obj).toString())) {
                        cell.setCellValue(ann.notEmpty());
                        j++;
                        continue;
                    }
                    if (f.get(obj) == null) {
                        j++;
                        continue;
                    }
                    //根据属性类型装入值
                    switch (type) {
                        case "char":
                        case "java.lang.Character":
                        case "class java.lang.String":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            cell.setCellValue(f.get(obj).toString());
                            break;
                        case "int":
                        case "class java.lang.Integer":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            cell.setCellValue(Integer.parseInt(f.get(obj).toString()));
                            break;
                        case "class java.util.Date":
//						    cell.getCellStyle().setDataFormat(format.getFormat("yyyy-m-d hh:mm"));
                            cell.setCellValue(f.get(obj).toString());
                            break;
                        case "float":
                        case "double":
                        case "class java.lang.Double":
                            String dString = new DecimalFormat("0").format(f.get(obj));
                            cell.setCellValue(dString);
                            break;
                        case "java.lang.Float":
                        case "java.lang.Long":
                        case "java.lang.Short":
                        case "class java.math.BigDecimal":
//							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//							String value = new BigDecimal() .toString();
                            cell.setCellValue(String.format("%.2f", f.get(obj)));
                            break;
                        default:
                            break;
                    }
                } catch (IllegalAccessException e) {
                    try {
                        System.out.println(f.get(obj).toString());
                    } catch (IllegalAccessException e1) {
                        e1.printStackTrace();
                    }
                    e.printStackTrace();
                }
                j++;
            }
        }
        //自动调整宽度
//        for(int a=0;a<i;a++) {
//            sheet.autoSizeColumn(a);
//        }
        return wb;
    }

    //自定义考勤表
    public static XSSFWorkbook exportExcel4(String sheetName, List data, Class clazz, Boolean isTran, String title, int Starttimes) {
        // 创建单元格和并项    43 行;  0开始
        Long date = Long.valueOf(Starttimes);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM");
        String sd = sdf.format(new Date(Long.parseLong(String.valueOf(date * 1000))));
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy  年  MM  月");
        String format1 = simpleDateFormat.format(new Date(Long.parseLong(String.valueOf(date * 1000))));
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy/MM/dd");
        sdf1.setLenient(false);
        SimpleDateFormat sdf2 = new SimpleDateFormat("EEE");
        if (isTran == null)
            isTran = false;
        if (isTran)
            data = EntityUtil.ListMapToListObj(data, clazz);
        if (StringUtils.isEmpty(sheetName))
            sheetName = "sheet1";
        XSSFWorkbook wb = null;
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new XSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        XSSFCellStyle cellStyle = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        font.setFontHeightInPoints((short) 22);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);// 加粗
        cellStyle.setFont(font);
        sheet.setColumnWidth((short)20,(short)20);
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(XSSFCellStyle.ALIGN_CENTER);


        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 47);
        CellRangeAddress cellRangeAddress5 = new CellRangeAddress(1, 1, 33, 47);
        CellRangeAddress cellRangeAddress1 = new CellRangeAddress(1, 1, 0, 32);
        //员工姓名
        CellRangeAddress cellRangeAddress_name = new CellRangeAddress(3, 4, 0, 0);
        CellRangeAddress cellRangeAddress_name2 = new CellRangeAddress(2, 2, 1, 47);
        //全月缺勤天数
        CellRangeAddress cellRangeAddress_work = new CellRangeAddress(3, 4, 32, 32);
        CellRangeAddress cellRangeAddress_out_work = new CellRangeAddress(3, 3, 33, 47);
        sheet.addMergedRegion(cellRangeAddress);
        sheet.addMergedRegion(cellRangeAddress_name);
        sheet.addMergedRegion(cellRangeAddress_name2);
        sheet.addMergedRegion(cellRangeAddress_work);
        sheet.addMergedRegion(cellRangeAddress5);
        sheet.addMergedRegion(cellRangeAddress1);
        sheet.addMergedRegion(cellRangeAddress_out_work);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row0 = sheet.createRow(0);
        XSSFCell cell0 = row0.createCell(0);
        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("和湛考勤表");
        XSSFRow row1 = sheet.createRow(1);
        XSSFCell cell34 = row1.createCell(33);
        cell34.setCellValue("考勤时间:" + format1);
        XSSFRow row2 = sheet.createRow(2);
        row2.createCell(1).setCellValue("实勤: 23天  休息/  事假#  婚假⊙  丧假◇  公假□   年假N  补休B  加班®   餐补C  病假   旷工×   产假V  陪产假Q   工伤+   迟到≧   早退≦   缺勤(未打卡)A  全勤(黄色)");
        XSSFRow row3 = sheet.createRow(3);
        XSSFCell cell33 = row3.createCell(33);
        cell33.setCellStyle(cellStyle1);
        cell33.setCellValue("全月缺勤工数");
        XSSFRow row4 = sheet.createRow(4);
        row4.setHeight((short) 400);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //创建标题
        //获取该实体所有定义的属性 返回Field数组
        Field[] fields = clazz.getDeclaredFields();
        int i = 0;
        for (Field field : fields) {
            //设置些属性是可以访问的
            field.setAccessible(true);
            //获取属性的类型
            String type = field.getGenericType().toString();
            boolean isAnn = field.isAnnotationPresent(ExcelAnnotation.class);
            if (!isAnn) {
                continue;
            }
            ExcelAnnotation ann = null;
            try {
                ann = field.getAnnotation(ExcelAnnotation.class);
            } catch (Exception e) {
                continue;
            }
            if (StringUtils.isEmpty(ann.rowName())) {
                continue;
            }
            //声明列对象
            XSSFCell cell = null;
            cell = row4.createCell(i);
            cell.setCellValue(ann.rowName());
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, ann.width());
            if (i > 0 && i < 32) {
                try {
                    Date date1 = sdf1.parse(sd + "/" + i);
                    row3.createCell(i).setCellValue( sdf2.format(date1).split("期")[1]);
                } catch (ParseException e) {
                    //do nothing
                }
            }else if(i==0 || i==32){
                row3.createCell(i).setCellValue(ann.rowName());
            }
            i++;
        }
            XSSFDataFormat format = wb.createDataFormat();
            //创建内容
            for (int n = 0; n < data.size(); n++) {
                row4 = sheet.createRow(n + 5);
                row4.setHeight((short) 400);
                Object obj = data.get(n);
                Class oClazz = obj.getClass();
                Field[] oFields = oClazz.getDeclaredFields();
                int j = 0;
                for (Field f : oFields) {
                    //设置些属性是可以访问的
                    f.setAccessible(true);
                    //获取属性的类型
                    String type = f.getGenericType().toString();
                    boolean isAnn = f.isAnnotationPresent(ExcelAnnotation.class);
                    if (!isAnn) {
                        continue;
                    }
                    ExcelAnnotation ann = null;
                    try {
                        ann = f.getAnnotation(ExcelAnnotation.class);
                    } catch (Exception e) {
                        continue;
                    }
                    if (StringUtils.isEmpty(ann.rowName())) {
                        continue;
                    }
                    XSSFCell cell = row4.createCell(j);
                    try {
                        if (!StringUtils.isEmpty(ann.notEmpty()) && f.get(obj) != null
                                && !StringUtils.isEmpty(f.get(obj).toString())) {
                            cell.setCellValue(ann.notEmpty());
                            j++;
                            continue;
                        }
                        if (f.get(obj) == null) {
                            j++;
                            continue;
                        }
                        //根据属性类型装入值
                        switch (type) {
                            case "char":
                            case "java.lang.Character":
                            case "class java.lang.String":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cell.setCellValue(f.get(obj).toString());
                                break;
                            case "int":
                            case "class java.lang.Integer":
//							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cell.setCellValue(Integer.parseInt(f.get(obj).toString()));
                                break;
                            case "class java.util.Date":
//						    cell.getCellStyle().setDataFormat(format.getFormat("yyyy-m-d hh:mm"));
                                cell.setCellValue(f.get(obj).toString());
                                break;
                            case "float":
                            case "double":
                            case "class java.lang.Double":
                                String dString = new DecimalFormat("0").format(f.get(obj));
                                cell.setCellValue(dString);
                                break;
                            case "java.lang.Float":
                            case "java.lang.Long":
                            case "java.lang.Short":
                            case "class java.math.BigDecimal":
//							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//							String value = new BigDecimal() .toString();
                                cell.setCellValue(String.format("%.2f", f.get(obj)));
                                break;
                            default:
                                break;
                        }
                    } catch (IllegalAccessException e) {
                        try {
                            System.out.println(f.get(obj).toString());
                        } catch (IllegalAccessException e1) {
                            e1.printStackTrace();
                        }
                        e.printStackTrace();
                    }
                    j++;
                }
            }
            return wb;
        }

}
